from openpyxl.styles import PatternFill, Alignment
from openpyxl import Workbook
from openpyxl import load_workbook
import random
import xlwings as xw
from openpyxl.styles import Font

# +++++++++++++++++++++++ 创建一个Excel文件，填充黄色并保存 ++++++++++++++++++++++
# 创建一个新的Excel文件
wb = Workbook()
# 获取当前正在操作的工作表
ws = wb.active

# 表格填充黄色
ws['A1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['B1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['C1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['D1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['G1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['H1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
ws['J1'].fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")

# 将工作簿保存到磁盘上
wb.save('D:/桌面/Patient.xlsx')
print('Excel创建成功！')

# ++++++++++++++++++++++++++++++++++++ 编写Excel文件的内容 +++++++++++++++++++++++++++++++++++++
StringBase = '\u7684\u4e00\u4e86\u662f\u6211\u4e0d\u5728\u4eba\u4eec\u6709\u6765\u4ed6\u8fd9\u4e0a\u7740\u4e2a\u5730' \
             '\u5230\u5927\u91cc\u8bf4\u5c31\u53bb\u5b50\u5f97\u4e5f\u548c\u90a3\u8981\u4e0b\u770b\u5929\u65f6\u8fc7' \
             '\u51fa\u5c0f\u4e48\u8d77\u4f60\u90fd\u628a\u597d\u8fd8\u591a\u6ca1\u4e3a\u53c8\u53ef\u5bb6\u5b66\u53ea' \
             '\u4ee5\u4e3b\u4f1a\u6837\u5e74\u60f3\u751f\u540c\u8001\u4e2d\u5341\u4ece\u81ea\u9762\u524d\u5934\u9053' \
             '\u5b83\u540e\u7136\u8d70\u5f88\u50cf\u89c1\u4e24\u7528\u5979\u56fd\u52a8\u8fdb\u6210\u56de\u4ec0\u8fb9' \
             '\u4f5c\u5bf9\u5f00\u800c\u5df1\u4e9b\u73b0\u5c71\u6c11\u5019\u7ecf\u53d1\u5de5\u5411\u4e8b\u547d\u7ed9' \
             '\u957f\u6c34\u51e0\u4e49\u4e09\u58f0\u4e8e\u9ad8\u624b\u77e5\u7406\u773c\u5fd7\u70b9\u5fc3\u6218\u4e8c' \
             '\u95ee\u4f46\u8eab\u65b9\u5b9e\u5403\u505a\u53eb\u5f53\u4f4f\u542c\u9769\u6253\u5462\u771f\u5168\u624d' \
             '\u56db\u5df2\u6240\u654c\u4e4b\u6700\u5149\u4ea7\u60c5\u8def\u5206\u603b\u6761\u767d\u8bdd\u4e1c\u5e2d' \
             '\u6b21\u4eb2\u5982\u88ab\u82b1\u53e3\u653e\u513f\u5e38\u6c14\u4e94\u7b2c\u4f7f\u5199\u519b\u5427\u6587' \
             '\u8fd0\u518d\u679c\u600e\u5b9a\u8bb8\u5feb\u660e\u884c\u56e0\u522b\u98de\u5916\u6811\u7269\u6d3b\u90e8' \
             '\u95e8\u65e0\u5f80\u8239\u671b\u65b0\u5e26\u961f\u5148\u529b\u5b8c\u5374\u7ad9\u4ee3\u5458\u673a\u66f4' \
             '\u4e5d\u60a8\u6bcf\u98ce\u7ea7\u8ddf\u7b11\u554a\u5b69\u4e07\u5c11\u76f4\u610f\u591c\u6bd4\u9636\u8fde' \
             '\u8f66\u91cd\u4fbf\u6597\u9a6c\u54ea\u5316\u592a\u6307\u53d8\u793e\u4f3c\u58eb\u8005\u5e72\u77f3\u6ee1' \
             '\u65e5\u51b3\u767e\u539f\u62ff\u7fa4\u7a76\u5404\u516d\u672c\u601d\u89e3\u7acb\u6cb3\u6751\u516b\u96be' \
             '\u65e9\u8bba\u5417\u6839\u5171\u8ba9\u76f8\u7814\u4eca\u5176\u4e66\u5750\u63a5\u5e94\u5173\u4fe1\u89c9' \
             '\u6b65\u53cd\u5904\u8bb0\u5c06\u5343\u627e\u4e89\u9886\u6216\u5e08\u7ed3\u5757\u8dd1\u8c01\u8349\u8d8a' \
             '\u5b57\u52a0\u811a\u7d27\u7231\u7b49\u4e60\u9635\u6015\u6708\u9752\u534a\u706b\u6cd5\u9898\u5efa\u8d76' \
             '\u4f4d\u5531\u6d77\u4e03\u5973\u4efb\u4ef6\u611f\u51c6\u5f20\u56e2\u5c4b\u79bb\u8272\u8138\u7247\u79d1' \
             '\u5012\u775b\u5229\u4e16\u521a\u4e14\u7531\u9001\u5207\u661f\u5bfc\u665a\u8868\u591f\u6574\u8ba4\u54cd' \
             '\u96ea\u6d41\u672a\u573a\u8be5\u5e76\u5e95\u6df1\u523b\u5e73\u4f1f\u5fd9\u63d0\u786e\u8fd1\u4eae\u8f7b' \
             '\u8bb2\u519c\u53e4\u9ed1\u544a\u754c\u62c9\u540d\u5440\u571f\u6e05\u9633\u7167\u529e\u53f2\u6539\u5386' \
             '\u8f6c\u753b\u9020\u5634\u6b64\u6cbb\u5317\u5fc5\u670d\u96e8\u7a7f\u5185\u8bc6\u9a8c\u4f20\u4e1a\u83dc' \
             '\u722c\u7761\u5174\u5f62\u91cf\u54b1\u89c2\u82e6\u4f53\u4f17\u901a\u51b2\u5408\u7834\u53cb\u5ea6\u672f' \
             '\u996d\u516c\u65c1\u623f\u6781\u5357\u67aa\u8bfb\u6c99\u5c81\u7ebf\u91ce\u575a\u7a7a\u6536\u7b97\u81f3' \
             '\u653f\u57ce\u52b3\u843d\u94b1\u7279\u56f4\u5f1f\u80dc\u6559\u70ed\u5c55\u5305\u6b4c\u7c7b\u6e10\u5f3a' \
             '\u6570\u4e61\u547c\u6027\u97f3\u7b54\u54e5\u9645\u65e7\u795e\u5ea7\u7ae0\u5e2e\u5566\u53d7\u7cfb\u4ee4' \
             '\u8df3\u975e\u4f55\u725b\u53d6\u5165\u5cb8\u6562\u6389\u5ffd\u79cd\u88c5\u9876\u6025\u6797\u505c\u606f' \
             '\u53e5\u533a\u8863\u822c\u62a5\u53f6\u538b\u6162\u53d4\u80cc\u7ec6'

# 打开Excel
wb = load_workbook('D:/桌面/Patient.xlsx')


# 手机号
def GetPhone():
    sj = random.randint(10000000, 99999999)
    sz = '138' + str(sj)
    return sz


# 出生日期
def GetBeBorn():
    sj1 = random.randint(50, 99)
    sj2 = random.randint(1, 9)
    sj3 = random.randint(10, 28)
    sz = '19' + str(sj1) + '-0' + str(sj2) + '-' + str(sj3)
    return sz


# 性别
def getGender():
    return random.choice(('男', '女'))

# 过敏史
def getAllergyHistory():
    return random.choice(('青霉素', '头孢', '磺胺', '无'))

# 既往病史
def getPastMedicalHistory():
    return random.choice(('高血压', '心脏病', '糖尿病', '无', '心悸', '气促', '紫绀', '水肿', '胸痛'))

# 病症
def getDisease():
    return random.choice(('头晕', '乏力', '抽搐', '痉挛', '尿血', '腹胀', '腹泻', '肢体麻木', '瘀斑'))

# 紧急联系电话
def GetEmergencyCall():
    sj = random.randint(10000000, 99999999)
    sz = '158' + str(sj)
    return sz


# 身份证号
def GetID():
    sj = random.randint(100000, 999999)
    sj2 = random.randint(1000000000, 9999999999)
    sz = str(sj) + '19' + str(sj2)
    return sz


# 单

def getNameOrAddress(flag):
    '''flag=1表示返回随机药名，flag=0表示返回随机地址'''
    result = ''
    if flag == 1:
        rangestart, rangeend = 1, 3
        ##姓名
    elif flag == 0:
        rangestart, rangeend = 6, 10
    else:
        print('flag must be 1 or 0')
    for i in range(random.randrange(rangestart, rangeend)):
        result += random.choice(StringBase)
        sj1 = random.choice(('赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈', '褚', '卫', '蒋', '沈', '韩',
                             '杨', '朱', '秦', '尹',
                             '许', '何', '吕', '施', '张', '孔', '曹', '华', '金', '魏', '陶', '姜', '戚', '谢', '邹',
                             '柏', '窦', '章', '云',
                             '苏', '潘', '葛', '奚', '范', '彭', '郎', '鲁', '韦', '昌', '马', '苗', '凤', '花', '方',
                             '俞', '任', '袁', '柳',
                             '酆', '鲍', '史', '唐', '费', '廉', '岑', '薛', '雷', '贺', '倪', '汤', '殷', '罗', '毕',
                             '郝', '邬', '安', '常',
                             '乐', '于', '时', '傅', '皮', '卞', '齐', '康', '伍', '余', '元', '卜', '顾', '孟', '平',
                             '黄', '和', '穆', '萧'))
        sz1 = str(sj1) + result
    return sz1


# 加载excel，注意路径要与脚本一致
# 激活excel表
sheet = wb.active
# 向excel中写入表头
sheet['a1'] = '姓名'
sheet['b1'] = '性别'
sheet['c1'] = '出生日期'
sheet['d1'] = '手机号'
sheet['e1'] = '紧急联系电话'
sheet['f1'] = '病人家属姓名'
sheet['g1'] = '既往病史'
sheet['h1'] = '过敏史'
sheet['i1'] = '病症'
sheet['j1'] = '身份证号'

for i in range(1999):
    name = getNameOrAddress(1)
    Gender = getGender()
    BeBorn = GetBeBorn()
    Phone = GetPhone()
    EmergencyCall = GetEmergencyCall()
    FamilyName = getNameOrAddress(1)
    PastMedicalHistory = getPastMedicalHistory()
    AllergyHistory = getAllergyHistory()
    Disease = getDisease()
    ID = GetID()
    sheet.append(
        [name, Gender, BeBorn, Phone, EmergencyCall, FamilyName, PastMedicalHistory, AllergyHistory, Disease, ID])

# 设置行高和列宽
for n in range(1, 10010):
    sheet.row_dimensions[n].height = 20
    sheet.column_dimensions["A"].width = 12
    sheet.column_dimensions["B"].width = 10
    sheet.column_dimensions["C"].width = 15
    sheet.column_dimensions["D"].width = 15
    sheet.column_dimensions["E"].width = 15
    sheet.column_dimensions["F"].width = 15
    sheet.column_dimensions["G"].width = 20
    sheet.column_dimensions["H"].width = 20
    sheet.column_dimensions["I"].width = 20
    sheet.column_dimensions["J"].width = 20

# ++++++++++++++++++++++++++++++++++++++++++++++++++++++  文字居中  ++++++++++++++++++++++
# 获取所有的sheet名
sheets = wb.sheetnames

# 读取第一个sheet表格
ws = wb[sheets[0]]
alignment_center = Alignment(horizontal='center', vertical='center')

# 指定区域单元格居中
ws_area = ws["A1:J10010"]
for i in ws_area:
    for j in i:
        j.alignment = alignment_center

# +++++++++++++++++++++++++++++++++++++++++++++++++++++  首行字体加粗  ++++++++++++++++++++++

# 获取所有的sheet名
sheets = wb.sheetnames

# 读取第一个sheet表格
ws = wb[sheets[0]]
font1 = Font(name="楷体")
font2 = Font(name="楷体", bold=True)

# 指定区域单元格字体
ws_area = ws["A1:J1"]
k = 0
for i in ws_area:
    k = k + 1
    for j in i:
        if k == 1:
            j.font = font2
        else:
            j.font = font1

wb.save('D:/桌面/Patient.xlsx')  # 保存表格 加路径
print('数据写入成功！')

# ++++++++++++++++++++++++++++++++ 表格加边框 +++++++++++++++++++++++++
# 打开存好的 Excel
app = xw.App()  # 设置应用
wb = xw.Book('D:/桌面/Patient.xlsx')  # 打开文件
ws = wb.sheets['Sheet']  # 选择表格

# 获取表格行列
last_column = ws.range(1, 1).end('right').get_address(0, 0)[0]  # 获取最后一列
last_row = ws.range(1, 1).end('down').row  # 获取最后一行
a_range = f'A1:{last_column}{last_row}'  # 生成表格的数据范围

# 设置边框
ws.range(a_range).api.Borders(8).LineStyle = 1  # 上边框
ws.range(a_range).api.Borders(9).LineStyle = 1  # 下边框
ws.range(a_range).api.Borders(7).LineStyle = 1  # 左边框
ws.range(a_range).api.Borders(10).LineStyle = 1  # 右边框
ws.range(a_range).api.Borders(12).LineStyle = 1  # 内横边框
ws.range(a_range).api.Borders(11).LineStyle = 1  # 内纵边框

# 保存并关闭 Excel
wb.save('D:/桌面/Patient.xlsx')
# wb.close()
# app.quit()
